package br.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.bean.DisciplinaTurmaBean;

public class DisciplinaTurmaDao {

	public void inserir (Connection conn, DisciplinaTurmaBean value) throws Exception{
		
		String sql = "INSERT INTO disciplina_turma (id_turma, id_diciplina, carga_horaria) VALUES (?, ?, ?)";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getIdturma().getId());
		ps.setInt(2, value.getId_diciplina().getId());
		ps.setInt(3, value.getCargahr());
		ps.execute();
		ps.close();
		
	}
	
	public void alterar (Connection conn, DisciplinaTurmaBean value) throws Exception{
		
		String sql = "UPDATE disciplina_turma SET id_turma = ?, id_diciplina = ?, carga_horaria = ? WHERE  id = ?";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getIdturma().getId());
		ps.setInt(2, value.getId_diciplina().getId());
		ps.setInt(3, value.getCargahr());
		ps.execute();
		ps.close();

	}
	
	public void excluir (Connection conn, DisciplinaTurmaBean value) throws Exception {

		String sql = "DELETE FROM disciplina_turma WHERE where id = ?";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ps.executeUpdate();
	}
	
	public ArrayList<DisciplinaTurmaBean> getLista(Connection conn, DisciplinaTurmaBean value) throws Exception{

		String sql = "SELECT id_turma, id_diciplina, carga_horaria FROM disciplina_turma where id = ?";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ResultSet rs = ps.executeQuery();
		ArrayList<DisciplinaTurmaBean> disciplinas = new ArrayList<DisciplinaTurmaBean>();
		while(rs.next()){
			DisciplinaTurmaBean a = new DisciplinaTurmaBean();
			a.getIdturma().setId(rs.getInt("id_turma"));
			a.getId_diciplina().setId(rs.getInt("id_diciplina"));
			a.setCargahr(rs.getInt("carga_horaria"));
			disciplinas.add(a);
		}
		return disciplinas;
	}
	
	public ArrayList<DisciplinaTurmaBean> getLista(Connection conn) throws Exception{

		String sql = "SELECT id, id_turma, id_diciplina, carga_horaria FROM disciplina_turma";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		ArrayList<DisciplinaTurmaBean> disciplinas = new ArrayList<DisciplinaTurmaBean>();
		while(rs.next()){
			DisciplinaTurmaBean a = new DisciplinaTurmaBean();
			a.setId(rs.getInt("id"));
			a.getIdturma().setId(rs.getInt("id_turma"));
			a.getId_diciplina().setId(rs.getInt("id_diciplina"));
			a.setCargahr(rs.getInt("carga_horaria"));
			disciplinas.add(a);
		}
		return disciplinas;
	}
	
	public List<DisciplinaTurmaBean> infoDidcTurma (Connection conn, int turma) throws SQLException{
		
		String sql = "SELECT  d.disciplina, dt.carga_horaria FROM disciplina d, disciplina_turma dt " +
				"WHERE d.id=dt.id_diciplina and dt.id_turma="+turma+" order by d.disciplina";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		
		List<DisciplinaTurmaBean> info = new ArrayList<DisciplinaTurmaBean>();
		DisciplinaTurmaBean d = null;
		while (rs.next()) {
			d = new DisciplinaTurmaBean();
			d.getId_diciplina().setDisciplina(rs.getString("disciplina"));
			d.setCargahr(rs.getInt("carga_horaria"));
			info.add(d);
		}
		return info;
	}
	
}
